﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using School.Entities;
using System.Collections;
using System.IO;
using System.Data.SqlClient;
using School.Admission.ReportsCls;

namespace School.Admission
{
    public class Student
    {
        private static SchoolEntities db = new SchoolEntities();

        public static IEnumerable<TE_Student> GetStudent(long FileNum)
        {
            var result = from c in db.TE_Student
                         where c.FileNumber== FileNum 
                         select c;

            return result;        
        }

        public static IEnumerable<TE_Student> GetStudentById(long Id)
        {
            var result = from c in db.TE_Student
                         where c.Id == Id
                         select c;

            return result;
        }

        public static object GetCommentsByTeacherIdStudentId(long TeacherId , long StudentId)
        {
            var result = from c in db.ORG_Comment
                         join s in db.TE_Student on c.StudentId equals s.Id
                         where c.TeacherId == TeacherId && c.StudentId == StudentId
                         select new { c.CommentId, c.Comment, c.ViewOnSite, FullName = s.Ar_FirstName + " " + s.Ar_SecondName + " " + s.Ar_ThirdNmae + " " + s.Ar_FourthName };
            return result;
        }

        public static object GetViewOnSiteCommentsByStudentId( long StudentId)
        {
            var result = from c in db.ORG_Comment
                         join s in db.ORG_Stuff on c.TeacherId equals s.Id
                         where c.StudentId == StudentId && c.ViewOnSite == true
                         select new { c.CommentId, c.Comment, c.ViewOnSite, TeacherName = s.FName + " " + s.MName + " " + s.LName };
            return result;
        }

        public static IEnumerable<ORG_Comment> GetCommentsById(long commentId)
        {
            var result = from c in db.ORG_Comment
                         where c.CommentId == commentId
                         select c;
            return result;
        }       

        public static void InsertComment( bool View, string comment, long teacherId, long studentId)
        {
            ORG_Comment _comment = new ORG_Comment();

           // _comment.Createdate = DateTime.Now;
            _comment.ViewOnSite = View;
            _comment.Comment = comment;
            _comment.TeacherId = teacherId;
            _comment.StudentId = studentId;
            db.ORG_Comment.AddObject(_comment);
            db.SaveChanges();

        }

        public static void updateComment(long commentId, bool View, string comment, long teacherId, long studentId)
        {
            ORG_Comment _comment = db.ORG_Comment.SingleOrDefault(x => x.CommentId == commentId);

            _comment.ViewOnSite = View;
            _comment.Comment = comment;
            _comment.TeacherId = teacherId;
            _comment.StudentId = studentId;          
            db.SaveChanges();
        }


        public static Boolean  Add(TE_Student student)
        {
            try
            {
                db.AddToTE_Student(student);
                db.SaveChanges();
                return true;
            }
            catch 
            {
                return false; 
            }
        }
        public static Int64 AddFromVoucher(TE_Student student)
        {
            try
            {
                db.AddToTE_Student(student);
                db.SaveChanges();
                return student.Id ;
            }
            catch
            {
                return -1;
            }
        }
        public static TE_Student Single(long id)
        {
            return db.TE_Student.Single(x => x.Id == id);
        }

        public static long  MaxID()
        {
            var query = (from q in db.TE_Student  select q.Id ).Max();
            return query;
        }
        public static IEnumerable<TE_Student> All(Hashtable items)
        {

            var query = from student in db.TE_Student select student;
            foreach (DictionaryEntry item in items)
            {
                if (item.Key == "Ar_FirstName")
                {
                    query = query.Where(c => c.Ar_FirstName == item.Value);
                }

                if (item.Key == "Ar_SecondName")
                {
                    query = query.Where(c => c.Ar_SecondName == item.Value);
                }

                if (item.Key == "Ar_ThirdNmae")
                {
                    query = query.Where(c => c.Ar_ThirdNmae == item.Value);                    
                }


                if (item.Key == "Ar_FourthName")
                {
                    query = query.Where(c => c.Ar_FourthName  == item.Value);
                }
                if (item.Key == "SD_FullName")
                {
                    switch (items["SearchType"].ToString() )
                    {
                        case "0":
                            query = query.Where(c => Convert.ToString(c.Ar_FirstName + c.Ar_SecondName + c.Ar_ThirdNmae + c.Ar_FourthName).StartsWith(item.Value.ToString()));
                        break;
                        case "1":
                        query = query.Where(c => Convert.ToString(c.Ar_FirstName + c.Ar_SecondName + c.Ar_ThirdNmae + c.Ar_FourthName).ToString().Contains(item.Value.ToString()));
                            break;
                        case "2":
                            query = query.Where(c => Convert.ToString(c.Ar_FirstName + c.Ar_SecondName + c.Ar_ThirdNmae + c.Ar_FourthName).ToString() == item.Value.ToString());
                            break;

                    }
                }
                
            }
               
            return query.ToList();
        }

        public static void Update(TE_Student student)
        {
            TE_Student c = db.TE_Student.Single(x => x.Id == student.Id);
            c.Id  = student.Id ;


            c.FileNumber = student.FileNumber;
            c.RegistrationNumber = student.RegistrationNumber;
            c.Certificate_Id = student.Certificate_Id;
            c.Stage_Id = student.Stage_Id;
            c.Grade_ID = student.Grade_ID;


            c.Ar_FirstName  = student.Ar_FirstName ;
            c.Ar_SecondName = student.Ar_SecondName;
            c.Ar_ThirdNmae = student.Ar_ThirdNmae;
            c.Ar_FourthName = student.Ar_FourthName;

            c.En_FirstName = student.En_FirstName;
            c.En_SecondName = student.En_SecondName;
            c.En_ThirdName = student.En_ThirdName;
            c.En_FourthName = student.En_FourthName;

            c.Gregorian_BirthDate = student.Gregorian_BirthDate;
            c.Hijri_BirthDate = student.Hijri_BirthDate;
            c.Gender_Id = student.Gender_Id;
            c.Religion_Id = student.Religion_Id;
            c.Country_Id = student.Country_Id;
            c.ID_Number = student.ID_Number;
            c.ID_Gregorian_Issue_Date = student.ID_Gregorian_Issue_Date;
            c.ID_Hijri_Issue_Date1 = student.ID_Hijri_Issue_Date1;
            c.Passport_Number = student.Passport_Number;
            c.Passport_Gregorian_Issue_Date = student.Passport_Gregorian_Issue_Date;
            c.Passport_Hijri_Issue_Date1 = student.Passport_Hijri_Issue_Date1;
            c.Arrangement_bt_Brothers = student.Arrangement_bt_Brothers;
            c.Used_Home_Languages = student.Used_Home_Languages;
            c.UserName = student.UserName;
            c.Password = student.Password;
           

            db.SaveChanges();

        }
        public static IEnumerable<object> AllFullName(int campId, int cerId, int stageId, int gradeId)
        {
            var query = from student in db.TE_Student
                        where  student.Certificate_Id == cerId && student.Stage_Id == stageId
                        && student.Grade_ID == gradeId && student.Campus_Id == campId
                        select new
                        {
                            Id = student.Id,
                            FullName = student.Ar_FirstName + " " + student.Ar_SecondName + " " + student.Ar_ThirdNmae + " " + student.Ar_FourthName
                        };
            return query.ToList();
        }
        public static Boolean Delete(long id)
        {
            try
            {
                db.TE_Student.DeleteObject(db.TE_Student.Single(x => x.Id == id));
                db.SaveChanges();
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static IEnumerable<TE_Student> Mysearch(Hashtable items,String RelTable,String RelTableDef)
        {

            String str;

            str= "SELECT     dbo.TE_Student.* ";
            str += " FROM         dbo.SD_QualificationSkill RIGHT OUTER JOIN " +
                      "dbo.SD_HomeAddress RIGHT OUTER JOIN " +
                      "dbo.SD_Mother ON dbo.SD_HomeAddress.Id = dbo.SD_Mother.HomeAddress_Id ON dbo.SD_QualificationSkill.Id = dbo.SD_Mother.Qualification_Id LEFT OUTER JOIN " +
                      "dbo.LU_Country ON dbo.SD_Mother.Nationality_Id = dbo.LU_Country.Id LEFT OUTER JOIN " +
                      "dbo.LU_Jobs ON dbo.SD_Mother.job_Id = dbo.LU_Jobs.Id RIGHT OUTER JOIN " + 
                      "dbo.SD_Custody LEFT OUTER JOIN " +
                      "dbo.SD_HomeAddress AS SD_HomeAddressCustody ON dbo.SD_Custody.HomeAddress_Id = SD_HomeAddressCustody.Id LEFT OUTER JOIN " +
                      "dbo.SD_ParentsSocialStatus ON dbo.SD_Custody.ParentsSocialStatus_Id = dbo.SD_ParentsSocialStatus.Id LEFT OUTER JOIN " +
                      "dbo.LU_Country AS LU_CountryCustody ON dbo.SD_Custody.Nationality_Id = LU_CountryCustody.Id LEFT OUTER JOIN " +
                      "dbo.SD_QualificationSkill AS SD_QualificationSkillCustody ON dbo.SD_Custody.Qualification_Id = SD_QualificationSkillCustody.Id LEFT OUTER JOIN " +
                      "dbo.SD_CustodyType ON dbo.SD_Custody.CustodyType_Id = dbo.SD_CustodyType.Id LEFT OUTER JOIN " +
                      "dbo.SD_LivingWith ON dbo.SD_Custody.StudentLivingPlace_Id = dbo.SD_LivingWith.Id RIGHT OUTER JOIN " +
                      "dbo.TE_Student ON dbo.SD_Custody.Student_Id = dbo.TE_Student.Id ON dbo.SD_Mother.Student_Id = dbo.TE_Student.Id LEFT OUTER JOIN " +
                      "dbo.SD_HomeAddress AS SD_HomeAddressFather RIGHT OUTER JOIN " +
                      "dbo.SD_Father ON SD_HomeAddressFather.Id = dbo.SD_Father.HomeAddress_Id RIGHT OUTER JOIN " +
                      "dbo.SD_QualificationSkill AS SD_QualificationSkillFather ON dbo.SD_Father.Qualification_Id = SD_QualificationSkillFather.Id LEFT OUTER JOIN " +
                      "dbo.LU_Country AS LU_CountryFather ON dbo.SD_Father.Nationality_Id = LU_CountryFather.Id ON dbo.TE_Student.Id = dbo.SD_Father.Student_Id " +
                      "WHERE     (1 = 1) ";
            foreach (DictionaryEntry item in items)
            {

                if (item.Key == "SD_FullName")
                {
                    str += " and dbo.TE_Student.Ar_FirstName + ' ' + dbo.TE_Student.Ar_SecondName + ' ' + dbo.TE_Student.Ar_ThirdNmae + ' ' + dbo.TE_Student.Ar_FourthName like '" + (item.Value.ToString()) + "'";
                }
                if (item.Key == "FA_FirstName")
                {
                    str += " and SD_Father.Ar_FirstName like '%" + (item.Value.ToString()) + "%'";
                }
                if (item.Key == "FileNumber")
                {
                    str += " and TE_Student.FileNumber = " + (item.Value.ToString()) ;
                }

                if (item.Key == "Status_Id")
                {
                    str += " and TE_Student.Status_Id = " + (item.Value.ToString());
                }

                
                if (item.Key == "Campus_Id")
                {
                    str += " and TE_Student.Campus_Id = " + (item.Value.ToString());
                }
                if (item.Key == "Certificate_Id")
                {
                    str += " and TE_Student.Certificate_Id = " + (item.Value.ToString());
                }
                if (item.Key == "Stage_Id")
                {
                    str += " and TE_Student.Stage_Id = " + (item.Value.ToString());
                }
                if (item.Key == "Grade_ID")
                {
                    str += " and TE_Student.Grade_ID = " + (item.Value.ToString());
                }
                if (item.Key == "RegistrationNumber")
                {
                    str += " and TE_Student.RegistrationNumber = " + (item.Value.ToString());
                }

                if (item.Key == "Relative_FullName")
                {
                    str += " and " + RelTable + ".Ar_FirstName + ' ' + " + RelTable + ".Ar_SecondName + ' ' + " + RelTable + ".Ar_ThirdName + ' ' + " + RelTable +  ".Ar_FourthName like '" + (item.Value.ToString()) + "'";
                }

                if (item.Key == "Specialization")
                {
                    str += " and " + RelTable + ".Specialization like '%" + (item.Value.ToString()) + "%'";
                }
                if (item.Key == "PointOfGraduation")
                {
                    str += " and " + RelTable + ".PointOfGraduation like '%" + (item.Value.ToString()) + "%'";
                }
                if (item.Key == "Nationality_Id")
                {
                    str += " and LU_Country" + RelTableDef + ".Id = " + (item.Value.ToString());

                }
                if (item.Key == "Qualification_Id")
                {
                    str += " and SD_QualificationSkill" + RelTableDef + ".Id = " + (item.Value.ToString());
                }
                if (item.Key == "Career_Id")
                {
                    str += " and  " + RelTable + ".job_Id = " + (item.Value.ToString());
                }
                if (item.Key == "ParentsSocialStatus_Id")
                {
                    str += " and dbo.SD_ParentsSocialStatus.Id = " + (item.Value.ToString());
                }
                if (item.Key == "StudentLivingPlace_Id")
                {
                    str += " and dbo.SD_LivingWith.Id = " + (item.Value.ToString());
                }
                if (item.Key == "CustodyType_Id")
                {
                    str += " and dbo.SD_CustodyType.Id = " + (item.Value.ToString());
                }
                if (item.Key == "District_Id")
                {
                    str += " and SD_HomeAddress" + RelTableDef + ".District_Id = " + (item.Value.ToString());

                }
                if (item.Key == "StreetName")
                {
                    str += " and SD_HomeAddress" + RelTableDef + ".StreetName like '%" + (item.Value.ToString()) + "%'";

                }


                if (item.Key == "FIdentification_Number")
                {
                    str += " and " + RelTable + ".Identification_Number = '" + (item.Value.ToString()) + "'";
                }
                
            }
            str += "  ORDER BY dbo.TE_Student.Campus_ID , dbo.TE_Student.Certificate_Id , dbo.TE_Student.Stage_Id, dbo.TE_Student.Grade_ID, dbo.TE_Student.Gender_Id, dbo.TE_Student.Ar_FirstName, dbo.TE_Student.Ar_SecondName, dbo.TE_Student.Ar_ThirdNmae, dbo.TE_Student.Ar_FourthName ";    
            return db.ExecuteStoreQuery<TE_Student>(str).ToList();  
        }
        public static IEnumerable<TE_Student> GetStudentsForExam(Int64 StatusID, Int64 StatusID2,Int64 campus, Int64 certifcate , Int64 stage, Int64 grade)
        {
            var query = from c in db.TE_Student
                        where c.Status_Id == StatusID
                        || c.Status_Id==StatusID2 
                        where c.Campus_Id == campus 
                        where c.Certificate_Id== certifcate
                        where c.Stage_Id==stage
                        where c.Grade_ID==grade 
                        select c;
            return query ;
        }
        public static IEnumerable<TE_Student> GetStudentsForClasses(Int64 StatusID, Int64 grade , String OrderType)
        {
            var query = from c in db.TE_Student
                        where c.Status_Id == StatusID
                        where c.Grade_ID == grade
                        select c;
            switch (OrderType) 
            {
                case "Birthday":
                    query = query.OrderBy(c => c.Gregorian_BirthDate);
                    break;
                case "Gender":
                    query = query.OrderBy(c => c.Gender_Id );
                    break;
                case "Alpha":
                    query = query.OrderBy(c => c.Ar_FirstName);
                    break;
            }
            return query.ToList();
        }

        public static void UpdateRegNum(TE_Student student)
        {
            TE_Student c = db.TE_Student.Single(x => x.Id == student.Id);
            c.Id = student.Id;

            c.RegistrationNumber = student.RegistrationNumber;
            db.SaveChanges();
        }


        public static void UpdateStatusId(TE_Student student)
        {
            TE_Student c = db.TE_Student.Single(x => x.Id == student.Id);
            c.Id = student.Id;

            c.Status_Id = student.Status_Id;
            db.SaveChanges();
        }
        public static Boolean CheckGradeToGrade(long grade1, long grade2)
        {
            try
            {
                ORG_Grade_To_Grade c = db.ORG_Grade_To_Grade.Single(x => (x.Grade_Id == grade1
                && x.Grade_Id2 == grade2));
                return true;
            }
            catch
            {
                try
                {
                    ORG_Grade_To_Grade c = db.ORG_Grade_To_Grade.Single(x => (x.Grade_Id == grade2
                && x.Grade_Id2 == grade1));

                    return true;
                }
                catch
                {                
                    return false;
                }

            }
        }
        public static IEnumerable<TE_Student> Mystudenttransaction(Hashtable items, String RelTable, String RelTableDef)
        {

            String str;

            str = "SELECT     dbo.TE_Student.Id, MAX(dbo.ADM_Student_Transaction.Status_Id) AS status_Id,dbo.TE_Student.RegistrationNumber , dbo.TE_Student.Campus_Id, dbo.TE_Student.Stage_Id, " +
                    "      dbo.TE_Student.Grade_ID, dbo.TE_Student.Certificate_Id, dbo.TE_Student.Ar_FirstName, dbo.TE_Student.Ar_SecondName, dbo.TE_Student.Ar_ThirdNmae,  " +
                     "     dbo.TE_Student.Ar_FourthName, dbo.TE_Student.FileNumber, dbo.TE_Student.Gregorian_BirthDate, dbo.TE_Student.En_FirstName, dbo.TE_Student.En_SecondName,  " +
                    "      dbo.TE_Student.En_ThirdName, dbo.TE_Student.En_FourthName, dbo.TE_Student.Hijri_BirthDate, dbo.TE_Student.Gender_Id, dbo.TE_Student.Religion_Id,  " +
                     "     dbo.TE_Student.Country_Id, dbo.TE_Student.ID_Number, dbo.TE_Student.ID_Gregorian_Issue_Date, dbo.TE_Student.ID_Hijri_Issue_Date1,  " +
                        "  dbo.TE_Student.Passport_Number, dbo.TE_Student.Passport_Hijri_Issue_Date1, dbo.TE_Student.Passport_Gregorian_Issue_Date,  " +
                        "  dbo.TE_Student.Arrangement_bt_Brothers, dbo.TE_Student.Used_Home_Languages " +
                        "  FROM         dbo.ORG_EductionalYear INNER JOIN " +
                        "  dbo.ADM_Student_Transaction INNER JOIN " +
                        "  dbo.TE_Student ON dbo.ADM_Student_Transaction.Student_Id = dbo.TE_Student.Id ON  " +
                        "  dbo.ORG_EductionalYear.Id = dbo.ADM_Student_Transaction.EductionalYear_Id LEFT OUTER JOIN " +
                        "  dbo.SD_Custody LEFT OUTER JOIN " +
                        "  dbo.SD_HomeAddress AS SD_HomeAddressCustody ON dbo.SD_Custody.HomeAddress_Id = SD_HomeAddressCustody.Id LEFT OUTER JOIN " +
                        "  dbo.SD_ParentsSocialStatus ON dbo.SD_Custody.ParentsSocialStatus_Id = dbo.SD_ParentsSocialStatus.Id LEFT OUTER JOIN " +
                        "  dbo.LU_Country AS LU_CountryCustody ON dbo.SD_Custody.Nationality_Id = LU_CountryCustody.Id LEFT OUTER JOIN " +
                        "  dbo.SD_QualificationSkill AS SD_QualificationSkillCustody ON dbo.SD_Custody.Qualification_Id = SD_QualificationSkillCustody.Id LEFT OUTER JOIN " +
                        "   dbo.SD_CustodyType ON dbo.SD_Custody.CustodyType_Id = dbo.SD_CustodyType.Id LEFT OUTER JOIN " +
                        "   dbo.SD_LivingWith ON dbo.SD_Custody.StudentLivingPlace_Id = dbo.SD_LivingWith.Id ON dbo.TE_Student.Id = dbo.SD_Custody.Student_Id LEFT OUTER JOIN " +
                        "  dbo.SD_QualificationSkill RIGHT OUTER JOIN " +
                        "   dbo.SD_HomeAddress RIGHT OUTER JOIN " +
                        "  dbo.SD_Mother ON dbo.SD_HomeAddress.Id = dbo.SD_Mother.HomeAddress_Id ON dbo.SD_QualificationSkill.Id = dbo.SD_Mother.Qualification_Id LEFT OUTER JOIN " +
                        "  dbo.LU_Country ON dbo.SD_Mother.Nationality_Id = dbo.LU_Country.Id LEFT OUTER JOIN " +
                        "  dbo.LU_Jobs ON dbo.SD_Mother.job_Id = dbo.LU_Jobs.Id ON dbo.TE_Student.Id = dbo.SD_Mother.Student_Id LEFT OUTER JOIN " +
                        "  dbo.SD_HomeAddress AS SD_HomeAddressFather RIGHT OUTER JOIN " +
                        "  dbo.SD_Father ON SD_HomeAddressFather.Id = dbo.SD_Father.HomeAddress_Id RIGHT OUTER JOIN " +
                    "      dbo.SD_QualificationSkill AS SD_QualificationSkillFather ON dbo.SD_Father.Qualification_Id = SD_QualificationSkillFather.Id LEFT OUTER JOIN " +
                   "       dbo.LU_Country AS LU_CountryFather ON dbo.SD_Father.Nationality_Id = LU_CountryFather.Id ON dbo.TE_Student.Id = dbo.SD_Father.Student_Id " +


        "  WHERE     (1 = 1)  ";

            foreach (DictionaryEntry item in items)
            {

                if (item.Key == "SD_FullName")
                {
                    str += "and dbo.TE_Student.Ar_FirstName + ' ' + dbo.TE_Student.Ar_SecondName + ' ' + dbo.TE_Student.Ar_ThirdNmae + ' ' + dbo.TE_Student.Ar_FourthName like '" + (item.Value.ToString()) + "'";
                }
                if (item.Key == "FA_FirstName")
                {
                    str += "and SD_Father.Ar_FirstName like '%" + (item.Value.ToString()) + "%'";
                }
                if (item.Key == "FileNumber")
                {
                    str += "and TE_Student.FileNumber = " + (item.Value.ToString());
                }
                if (item.Key == "Campus_Id")
                {
                    str += "and TE_Student.Campus_Id = " + (item.Value.ToString());
                }
                if (item.Key == "Certificate_Id")
                {
                    str += "and TE_Student.Certificate_Id = " + (item.Value.ToString());
                }
                if (item.Key == "Stage_Id")
                {
                    str += "and TE_Student.Stage_Id = " + (item.Value.ToString());
                }



                if (item.Key == "Grade_ID")
                {
                    str += "and TE_Student.Grade_ID = " + (item.Value.ToString());
                }

                if (item.Key == "RegistrationNumber")
                {
                    str += "and TE_Student.RegistrationNumber = " + (item.Value.ToString());
                }



                if (item.Key == "Status_Id")
                {
                    str += "and  ADM_Student_Transaction.Status_Id = " + (item.Value.ToString());
                }


                if (item.Key == "Relative_FullName")
                {
                    str += "and " + RelTable + ".Ar_FirstName + ' ' + " + RelTable + ".Ar_SecondName + ' ' + " + RelTable + ".Ar_ThirdName + ' ' + " + RelTable + ".Ar_FourthName like '" + (item.Value.ToString()) + "'";
                }

                if (item.Key == "Specialization")
                {
                    str += "and " + RelTable + ".Specialization like '%" + (item.Value.ToString()) + "%'";
                }
                if (item.Key == "PointOfGraduation")
                {
                    str += "and " + RelTable + ".PointOfGraduation like '%" + (item.Value.ToString()) + "%'";
                }
                if (item.Key == "Nationality_Id")
                {
                    str += "and LU_Country" + RelTableDef + ".Id = " + (item.Value.ToString());

                }
                if (item.Key == "Qualification_Id")
                {
                    str += "and SD_QualificationSkill" + RelTableDef + ".Id = " + (item.Value.ToString());
                }
                if (item.Key == "Career_Id")
                {
                    str += "and LU_Jobs" + RelTableDef + ".Id = " + (item.Value.ToString());
                }
                if (item.Key == "ParentsSocialStatus_Id")
                {
                    str += "and dbo.SD_ParentsSocialStatus.Id = " + (item.Value.ToString());
                }
                if (item.Key == "StudentLivingPlace_Id")
                {
                    str += "and dbo.SD_LivingWith.Id = " + (item.Value.ToString());
                }
                if (item.Key == "CustodyType_Id")
                {
                    str += "and dbo.SD_CustodyType.Id = " + (item.Value.ToString());
                }
                if (item.Key == "District_Id")
                {
                    str += "and SD_HomeAddress" + RelTableDef + ".District_Id = " + (item.Value.ToString());

                }
                if (item.Key == "StreetName")
                {
                    str += "and SD_HomeAddress" + RelTableDef + ".StreetName like '%" + (item.Value.ToString()) + "%'";

                }


                if (item.Key == "Identification_Number")
                {
                    str += "and " + RelTable + ".Identification_Number = '" + (item.Value.ToString()) + "'";
                }


            }

            str += " GROUP BY dbo.TE_Student.Id, dbo.TE_Student.RegistrationNumber , dbo.TE_Student.Campus_Id, dbo.TE_Student.Stage_Id, dbo.TE_Student.Grade_ID, dbo.TE_Student.Certificate_Id, dbo.TE_Student.Ar_FirstName, " +
                    "  dbo.TE_Student.Ar_SecondName, dbo.TE_Student.Ar_ThirdNmae, dbo.TE_Student.Ar_FourthName, dbo.TE_Student.FileNumber, dbo.TE_Student.Gregorian_BirthDate,  " +
                     "   dbo.TE_Student.En_FirstName, dbo.TE_Student.En_SecondName, dbo.TE_Student.En_ThirdName, dbo.TE_Student.En_FourthName, dbo.TE_Student.Hijri_BirthDate,  " +
                      "  dbo.TE_Student.Gender_Id, dbo.TE_Student.Religion_Id, dbo.TE_Student.Country_Id, dbo.TE_Student.ID_Number, dbo.TE_Student.ID_Gregorian_Issue_Date,  " +
                      "  dbo.TE_Student.ID_Hijri_Issue_Date1, dbo.TE_Student.Passport_Number, dbo.TE_Student.Passport_Hijri_Issue_Date1,  " +
                     "   dbo.TE_Student.Passport_Gregorian_Issue_Date, dbo.TE_Student.Arrangement_bt_Brothers, dbo.TE_Student.Used_Home_Languages " +
                     "  ORDER BY dbo.TE_Student.Gregorian_BirthDate ";


            return db.ExecuteStoreQuery<TE_Student>(str).ToList();

        }
        public static void UpdateGrade(TE_Student student)
        {
            TE_Student c = db.TE_Student.Single(x => x.Id == student.Id);
            c.Id = student.Id;
            c.Status_Id = student.Status_Id;
            c.Campus_Id = student.Campus_Id;
            c.Certificate_Id = student.Certificate_Id;
            c.Stage_Id = student.Stage_Id ;
            c.Grade_ID = student.Grade_ID;
            db.SaveChanges();
        }
        public static IEnumerable<object> SimpleGetStudent(Hashtable items)
        {

            var query = from student in db.TE_Student select                        
                        new 
                        {
                            Id = student.Id,
                            FullName = student.Ar_FirstName + " " + student.Ar_SecondName + " " + student.Ar_ThirdNmae + " " +  student.Ar_FourthName,
                            CampusName = student.ORG_Campus.Name,
                            Stage_Id = student.Stage_Id ,
                            Certificate_Id = student.Certificate_Id,
                            Campus_Id = student.Campus_Id,
                            Grade_ID = student.Grade_ID,
                            CetificateName = student.ORG_Certificate.Name,
                            StageName = student.ORG_Stage.Name,
                            GradeName = student.ORG_Grade.Name,
                            Status_Id = student.Status_Id,
                            student.Gender_Id

                        };

            var whole = query.Except(query);

            if (items.Count == 0)
                return whole;

            foreach (DictionaryEntry item in items)
            {
                if (item.Key == "Campus_Id")
                {
                    var value = long.Parse(item.Value.ToString());
                    whole = query.Where(c => c.Campus_Id == value).Union(whole);
                }
                if (item.Key == "Certificate_Id")
                {
                    var value = long.Parse(item.Value.ToString());
                    whole = query.Where(c => c.Certificate_Id == value).Union(whole);
                }
                if (item.Key == "Stage_Id")
                {
                    var value = long.Parse(item.Value.ToString());
                    whole = query.Where(c => c.Stage_Id == value).Union(whole);
                }
                if (item.Key == "Grade_ID")
                {
                    var value = long.Parse(item.Value.ToString());
                    whole = query.Where(c => c.Grade_ID == value).Union(whole);
                }
                if (item.Key == "NotStatus_Id")
                {
                    var value = long.Parse(item.Value.ToString());
                    whole = query.Where(c => c.Status_Id != value).Union(whole);
                }

                if (item.Key == "SD_FullName")
                {
                    String name = Convert.ToString(item.Value);
                    switch (items["SearchType"].ToString())
                    {
                        case "0":
                            whole = query.Where(c => c.FullName.StartsWith(name));
                            break;
                        case "1":
                            whole = query.Where(c => c.FullName.Contains(name));
                            break;
                        case "2":
                            whole = query.Where(c => c.FullName == name);
                            break;

                    }
                }

            }

            return (from x in whole.Distinct() orderby x.Gender_Id, x.FullName select x).ToList();
        }

        public static  IEnumerable<ADM_Status_Type> GetAllstatus()
        {
            var query = from status in db.ADM_Status_Type
                        select status;
            return query;

        }

        public IQueryable<rptStudentStatuscls> GetStudentStatusRep(int cerId,int? grade_Id,int? statusId)
        {

            var query = from student in db.TE_Student 
                        where student.Stage_Id == cerId
                        select new ReportsCls.rptStudentStatuscls
                        {
                            Id = student.Id,
                            studentFullName = student.Ar_FirstName + " " + student.Ar_SecondName  + " " + student.Ar_ThirdNmae + " " + student.Ar_FourthName,
                            studentFileNumber = student.FileNumber,
                            Campus = student.ORG_Campus.Name,
                            Certificate = student.ORG_Certificate.Name,
                            Stage = student.ORG_Stage.Name,
                            Grade = student.ORG_Grade.Name,
                            StatusName = student.ADM_Status_Type.Name,
                            Grade_Id=student.Grade_ID,
                            Status_Id= student.Status_Id
                        };
            if (grade_Id != 0)
                query = query.Where(c => c.Grade_Id == grade_Id);

            if (statusId != 0)
                query = query.Where(c => c.Status_Id == statusId);
            return query;

        }

        public TE_Student GetByUserNamePassword(String UserName, String pass)
        {
            var data = from x in db.TE_Student
                       where x.UserName == UserName
                       && x.Password == pass
                       select x;

            if (data.Count() > 0)
                return data.First();
            else
                return null;
        }
        public static bool CheckUserNameFound(string userName, long studentId)
        {
            return (db.TE_Student.SingleOrDefault(i => i.UserName.ToLower() == userName.ToLower() && i.Id != studentId) != null);
        }
    }

}
